package com.matt.repository.courseV2;

import com.matt.domain.courseV2.VendorCoursePackage;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface VendorCoursePackageRepository extends JpaRepository<VendorCoursePackage, String>, JpaSpecificationExecutor<VendorCoursePackage> {

    List<VendorCoursePackage> findAll();

    @Query(value ="SELECT cp.* FROM tc_course_package cp LEFT JOIN  tc_area_course_package acp  ON acp.course_package_id = cp.id" +
            " WHERE (acp.deleted = 0 AND acp.avaliable_date > NOW() AND cp.avaliable_date > NOW() AND acp.area_id = :areaId  " +
            "  or cp.price= 0 )   AND   if(:name  !='', cp.name  LIKE  concat('%', :name, '%') , 1=1)  AND   if(:type  !=-1, cp.typeName  LIKE  concat('%', :type, '%') , 1=1)  and  parentId = :parentId and cp.deleted = 0 ORDER BY cp.orderNum  LIMIT :num , :size ",nativeQuery = true)
    List<VendorCoursePackage>  areaIdByCoursePackageList(@Param("areaId")String areaId, @Param("type")int type, @Param("name")String name,@Param("parentId")String parentId,@Param("num")int  num,@Param("size")int  size);


    @Query(value ="SELECT count(cp.id) FROM tc_course_package cp LEFT JOIN  tc_area_course_package acp  ON acp.course_package_id = cp.id" +
            "  WHERE (acp.deleted = 0 AND acp.avaliable_date > NOW() AND cp.avaliable_date > NOW() AND acp.area_id = :areaId   " +
            "  or cp.price= 0  )  AND   if(:name  !='', cp.name  LIKE  concat('%', :name, '%') , 1=1)   and  parentId = :parentId   and cp.deleted = 0 AND   if(:type  !=-1,  cp.typeName  LIKE  concat('%', :type, '%') , 1=1)   ",nativeQuery = true)
    Integer areaIdByCoursePackageCount(@Param("areaId")String areaId, @Param("type")int type, @Param("name")String name,@Param("parentId")String parentId);

    VendorCoursePackage findByNameAndDimensionsAndPrice(String name,String dimensions,Integer price);

    @Query(value ="SELECT * FROM `tc_course_package` where age_low <=  :month  and age_high >= :month or (age_low is null  and age_high is null)  and deleted = 0  ORDER BY orderNum",nativeQuery = true)
    List<VendorCoursePackage> findByMonth(Integer month);

    @Query(value ="SELECT * FROM `tc_course_package` where parentId = 0  and (age_low <=  :month  and age_high >= :month or (age_low is null  and age_high is null))  and  if(:typeName  !='', typeName LIKE  concat('%', :typeName, '%')  , 1=1)   and deleted = 0  ORDER BY orderNum",nativeQuery = true)
    List<VendorCoursePackage> findByMonthAndTypeName(Integer month,String typeName);

    @Query(value ="SELECT * FROM `tc_course_package` where parentId = 0   and  if(:typeName  !='', typeName LIKE  concat('%', :typeName, '%')  , 1=1)  and deleted = 0  ORDER BY orderNum",nativeQuery = true)
    List<VendorCoursePackage> findByTypeName(String typeName);

    @Query(value ="SELECT * FROM `tc_course_package` where (parentId = :parentId   or  id = :id)  and deleted = 0 ORDER BY orderNum ",nativeQuery = true)
    List<VendorCoursePackage> findByParentIdOrId(String parentId,String id);

    @Query(value ="SELECT * FROM `tc_course_package` where parentId = :parentId   and deleted = 0 ORDER BY orderNum",nativeQuery = true)
    List<VendorCoursePackage> findByParentIdOrderByCreatedAsc(String parentId);

    @Query(value ="SELECT * FROM `tc_course_package` where parentId in :parentIds  and deleted = 0  ORDER BY orderNum",nativeQuery = true)
    List<VendorCoursePackage> findByParentIdIn(List<String> parentIds);

}